package drugdealer.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import drugdealer.exception.UserTypeException;
import drugdealer.model.Medico;

/**
 * Helper class to manage DB related functions for the "Medico" class.
 * 
 * @author leonardo
 *
 */

public class MedicoDAO {
	/**
	 * Static method to insert a new medic into the database.
	 * This method does not check the validity of data in "Medico" object
	 * passed as parameter "medico".
	 * 
	 * @param usuario is the user to be added.
	 * @return different than 0 if successful, 0 otherwise.
	 * @throws UserTypeException 
	 */
	public static int insert(Medico medico) throws UserTypeException {
		// Get ConnectionPool
		ConnectionPool pool = ConnectionPool.getInstance();

		// Open DB connection
		Connection connection = pool.getConnection();

		// PreparedStatement contains a query template
		// which can be dynamically populated
		PreparedStatement ps = null;

		// Query templates
		// For UsuarioDados
		String queryUsuarioDados = "INSERT INTO UsuarioDados (Email, Nome, RG, Endereco, Aniversario)"
				+ "VALUES (?, ?, ?, ?, ?)";
		
		// For UsuarioSenha
		String queryUsuarioSenha = "INSERT INTO UsuarioSenha (Email, Senha)"
				+ "VALUES (?, ?)";
		
		// For UsuarioSenha
		String queryUsuarioTipo = "INSERT INTO UsuarioTipo (Email, Tipo)"
				+ "VALUES (?, ?)";
		
		// For MedicoDados
		String queryMedicoDados = "INSERT INTO MedicoDados (Email, CRM)"
				+ "VALUES (?, ?)";

		try {
			int result = 0;
			
			/**************
			  UsuarioDados
			 **************/
			// Populate the query template for UsuarioDados table
			ps = connection.prepareStatement(queryUsuarioDados);
			ps.setString(1, medico.getEmail());
			ps.setString(2, medico.getNome());
			ps.setString(3, medico.getRG());
			ps.setString(4, medico.getEndereco());
			ps.setDate(5, medico.getAniversario());

			// WARNING: "aniversario" is java.sql.Date type NOT java.util.Date
			// TODO The date must be formatted into string format "yyyy-mm-dd"
			ps.setDate(5, (Date) medico.getAniversario());

			// Insert data into UsuarioDados table
			result += ps.executeUpdate();
			
			/**************
			  UsuarioSenha
			 **************/
			// Populate the query template for UsuarioSenha table
			ps = connection.prepareStatement(queryUsuarioSenha);
			ps.setString(1, medico.getEmail());
			ps.setString(2, medico.getSenha());
			
			// Insert data into UsuarioSenha table
			result += ps.executeUpdate();
			
			/*************
			  UsuarioTipo
			 *************/
			// Populate the query template for UsuarioSenha table
			ps = connection.prepareStatement(queryUsuarioTipo);
			ps.setString(1, medico.getEmail());


			ps.setString(2, "medico");
			
			// Insert data into UsuarioSenha table
			result += ps.executeUpdate();
			
			/*************
			  MedicoDados
			 *************/
			// Populate the query template for MedicoDados table
			ps = connection.prepareStatement(queryMedicoDados);
			ps.setString(1, medico.getEmail());
			ps.setString(2, medico.getCRM());
			
			// Insert data into MedicoDados table
			result += ps.executeUpdate();
			
			
			return result;
		} catch (SQLException sqle) {
			// todo exceptions throwing
			sqle.printStackTrace();
			return 0;
		} finally {
			pool.freeConnection(connection);
		}
	}
}
